package com.sun.myproject.daoimpl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sun.myproject.bean.Book;
import com.sun.myproject.bean.PageBean;
import com.sun.myproject.dao.BookDao;
import com.sun.myproject.tool.JDBCUtil;
import com.sun.myproject.tool.TextUtils;

public class BookDaoImpl implements BookDao {

	@Override
	public List<Book> findBookByPage(int currentPage, String bookid, String bookName) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		System.out.println(bookid);
		System.out.println(bookName);
		if (!"".equals(bookid) && null != bookid) {
			int id=Integer.parseInt(bookid);
			String s = "%" + bookid + "%";
			System.out.println("-------====----" + s);
			return runner.query("select * from book where bookid LIKE ? limit ? offset ?",
					new BeanListHandler<Book>(Book.class), s, PAGE_SIZE, (currentPage - 1) * PAGE_SIZE);
		} else if (!"".equals(bookName) && null != bookName) {
			String s = "%" + bookName + "%";
			System.out.println("-------====----" + s);
			return runner.query("select * from book where bookname LIKE ? limit ? offset ?",
					new BeanListHandler<Book>(Book.class), s, PAGE_SIZE, (currentPage - 1) * PAGE_SIZE);
		}
		return runner.query("select * from book limit ? offset ?", new BeanListHandler<Book>(Book.class), PAGE_SIZE,
				(currentPage - 1) * PAGE_SIZE);
	}

	@Override
	public List<Book> findAll() throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		return runner.query("select * from book", new BeanListHandler<Book>(Book.class));
	}

	@Override
	public void insert(Book Book) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		runner.update("insert into book (bookname,publish,author,bookcount,booktype,img) values(?,?,?,?,?,?)",
				Book.getBookname(), Book.getPublish(), Book.getAuthor(), Book.getBookcount(), Book.getBooktype(),
				Book.getImg());
	}

	@Override
	public List<Book> searchBook(String Bookname, String Booktype) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		String sql = "select * from book where 1=1 ";
		List<String> list = new ArrayList<String>();
		if (!TextUtils.isEmpty(Bookname)) {
			sql = sql + "  and bookname like ?";
			list.add("%" + Bookname + "%");
		}
		System.out.println(Bookname);
		if (!TextUtils.isEmpty(String.valueOf(Booktype))) {
			sql = sql + " and booktype = ?";
			list.add(String.valueOf(Booktype));
		}
		System.out.println(Booktype);
		System.out.println(sql);
		return runner.query(sql, new BeanListHandler<Book>(Book.class), list.toArray());
	}

	@Override
	public void delete(int Bookid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		System.out.println("delete from book where bookid = " + Bookid);
		runner.update("delete from book where bookid = " + Bookid);
	}

	@Override
	public void update(Book Book) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		runner.update("update book set `bookname`=?,`publish`=?,`author`=?,`bookcount`=?,`booktype`= ?,img=? where bookid=?",
				Book.getBookname(), Book.getPublish(), Book.getAuthor(), Book.getBookcount(), Book.getBooktype(),Book.getImg()
				,Book.getBookid());
	}

	@Override
	public Book findBookById(int Bookid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		return runner.query("select * from book where bookid =?", new BeanHandler<Book>(Book.class), Bookid);
	}

	@Override
	public int findCount(String bookid, String bookName) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		if (!"".equals(bookid) && null != bookid) {
			int id=Integer.parseInt(bookid);
			String s = "%" + id + "%";
			System.out.println("-------====----" + s);
			Long result = (Long) runner.query("SELECT COUNT(*) FROM book where bookid LIKE ?", new ScalarHandler(),
					s);
			return result.intValue();
		} else if (!"".equals(bookName) && null != bookName) {
			String s = "%" + bookName + "%";
			System.out.println("-------====----" + s);
			Long result = (Long) runner.query("SELECT COUNT(*) FROM book where bookname LIKE ?", new ScalarHandler(),
					s);
			return result.intValue();
		}
		Long result = (Long) runner.query("SELECT COUNT(*) FROM book", new ScalarHandler());
		return result.intValue();
	}

	@Override
	public PageBean findBookByPages(int currentPage, String bookid, String bookName) throws SQLException {
		// 封装分页的该页数据
		PageBean<Book> pageBean = new PageBean<Book>();

		int pageSize = BookDao.PAGE_SIZE;
		pageBean.setCurrentPage(currentPage); // 设置当前页
		pageBean.setPageSize(pageSize); // 设置每页显示多少记录

		BookDao dao = new BookDaoImpl();
		List<Book> list = dao.findBookByPage(currentPage, bookid, bookName);
		System.out.println(list.toString());
		pageBean.setList(list); // 设置这一页的学生数据
		// 总的记录数， 总的页数。
		int count = dao.findCount(bookid, bookName);
		pageBean.setTotalSize(count); // 设置总的记录数
		// 200 ， 10 ==20 201 ， 10 = 21 201 % 10 == 0 ?201 / 10 :201 % 10 + 1
		pageBean.setTotalPage(count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1); // 总页数
		return pageBean;
	}

	@Override
	public List<Book> exportExcel(String bookName) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		if (!"".equals(bookName) && null != bookName) {
			String s = "%" + bookName + "%";
			System.out.println("-----%%%%%--====----" + s);
			return runner.query("select * from book where bookname LIKE ?", new BeanListHandler<Book>(Book.class), s);
		} else {
			System.out.println("-----%%%%%--====--000000--");
			return findAll();
		}
	}

}
